package me.bay.gt.java;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBC1Exercise {
    private final String dbUrl;
    private final String dbUser;
    private final String dbPassword;

    public JDBC1Exercise(String dbUrl, String dbUser, String dbPassword) {
        this.dbUrl = dbUrl;
        this.dbUser = dbUser;
        this.dbPassword = dbPassword;
    }

    public Hero selectById(int id) throws SQLException {
        long startMills = System.currentTimeMillis();
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement()) {
            String sql = "SELECT * FROM heroes WHERE id = " + id;
            ResultSet rs = stat.executeQuery(sql);
            if (rs != null && rs.next()) {
                Hero hero = new Hero();
                hero.setId(rs.getInt("id"));
                hero.setName(rs.getString("name"));
                hero.setHpMax(rs.getObject("hp_max", Float.class));
                hero.setHpGrowth(rs.getObject("hp_growth", Float.class));
                hero.setHpStart(rs.getObject("hp_start", Float.class));
                hero.setMpMax(rs.getObject("mp_max", Float.class));
                hero.setMpGrowth(rs.getObject("mp_growth", Float.class));
                hero.setMpStart(rs.getObject("mp_start", Float.class));
                hero.setAttackMax(rs.getObject("attack_max", Float.class));
                hero.setAttackGrowth(rs.getObject("attack_growth", Float.class));
                hero.setAttackStart(rs.getObject("attack_start", Float.class));
                hero.setDefenseMax(rs.getObject("defense_max", Float.class));
                hero.setDefenseGrowth(rs.getObject("defense_growth", Float.class));
                hero.setDefenseStart(rs.getObject("defense_start", Float.class));
                hero.setHp5sMax(rs.getObject("hp_5s_max", Float.class));
                hero.setHp5sGrowth(rs.getObject("hp_5s_growth", Float.class));
                hero.setHp5sStart(rs.getObject("hp_5s_start", Float.class));
                hero.setMp5sMax(rs.getObject("mp_5s_max", Float.class));
                hero.setMp5sGrowth(rs.getObject("mp_5s_growth", Float.class));
                hero.setMp5sStart(rs.getObject("mp_5s_start", Float.class));
                hero.setAttackSpeedMax(rs.getObject("attack_speed_max", Float.class));
                hero.setAttackRange(rs.getString("attack_range"));
                hero.setRoleMain(rs.getString("role_main"));
                hero.setRoleAssist(rs.getString("role_assist"));
                Date birthdate = rs.getDate("birthdate");
                if (birthdate != null) {
                    hero.setBirthdate(birthdate.toLocalDate());
                }
                return hero;
            }
        } finally {
            long endMills = System.currentTimeMillis();
            System.out.println("selectById " + id + ": " + (endMills - startMills) + " ms.");
        }
        return null;
    }

    public void insert(Hero hero) throws SQLException {
        //  37, 1.571, 15, 0, '近战', '坦克', '战士', '2016-07-19');

        try (Connection conn = getConnection();
             Statement stat = conn.createStatement()) {
            String sql = "INSERT INTO heroes (`name`, " +
                    "`hp_max`, `hp_growth`, `hp_start`, " +
                    "`mp_max`, `mp_growth`, `mp_start`, " +
                    "`attack_max`, `attack_growth`, `attack_start`, " +
                    "`defense_max`, `defense_growth`, `defense_start`, " +
                    "`hp_5s_max`, `hp_5s_growth`, `hp_5s_start`, " +
                    "`mp_5s_max`, `mp_5s_growth`, `mp_5s_start`, " +
                    "`attack_speed_max`, `attack_range`, `role_main`, `role_assist`, `birthdate`)" +
                    "VALUES ('" + hero.getName() + "', " +
                    hero.getHpMax() + ", " + hero.getHpGrowth() + ", " + hero.getHpStart() + ", " +
                    hero.getMpMax() + ", " + hero.getMpGrowth() + ", " + hero.getMpStart() + ", " +
                    hero.getAttackMax() + ", " + hero.getAttackGrowth() + ", " + hero.getAttackStart() + ", " +
                    hero.getDefenseMax() + ", " + hero.getDefenseGrowth() + ", " + hero.getDefenseStart() + ", " +
                    hero.getHp5sMax() + ", " + hero.getHp5sGrowth() + ", " + hero.getHp5sStart() + ", " +
                    hero.getMp5sMax() + ", " + hero.getMp5sGrowth() + ", " + hero.getMp5sStart() + ", " +
                    hero.getAttackSpeedMax() + ", '" + hero.getAttackRange() + "', '" +
                    hero.getRoleMain() + "', '" + hero.getRoleAssist() + "', '" + hero.getBirthdate() + "')";
            int result = stat.executeUpdate(sql);
            System.out.println("insert result = " + result);
        }
    }

    public void updateById(Hero hero) throws SQLException {
        if (hero.getId() == null) {
            System.err.println("INVALID ID OF HERO");
            return;
        }
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE heroes SET ");
        List<String> kvList = new ArrayList<>();
        if (hero.getName() != null) {
            kvList.add("name = '" + hero.getName() + "'");
        }
        if (hero.getHpMax() != null) {
            kvList.add("hp_max = " + hero.getHpMax());
        }
        if (hero.getHpGrowth() != null) {
            kvList.add("hp_growth = " + hero.getHpGrowth());
        }
        if (hero.getHpStart() != null) {
            kvList.add("hp_start = " + hero.getHpStart());
        }
        if (hero.getMpMax() != null) {
            kvList.add("mp_max = " + hero.getMpMax());
        }
        if (hero.getMpGrowth() != null) {
            kvList.add("mp_growth = " + hero.getMpGrowth());
        }
        if (hero.getMpStart() != null) {
            kvList.add("mp_start = " + hero.getMpStart());
        }
        if (hero.getAttackMax() != null) {
            kvList.add("attack_max = " + hero.getAttackMax());
        }
        if (hero.getAttackStart() != null) {
            kvList.add("attack_start = " + hero.getAttackStart());
        }
        if (hero.getHp5sMax() != null) {
            kvList.add("hp_5s_max = " + hero.getHp5sMax());
        }
        if (hero.getHp5sGrowth() != null) {
            kvList.add("hp_5s_growth = " + hero.getHp5sGrowth());
        }
        if (hero.getHp5sStart() != null) {
            kvList.add("hp_5s_start = " + hero.getHp5sStart());
        }
        if (hero.getMp5sMax() != null) {
            kvList.add("mp_5s_max = " + hero.getMp5sMax());
        }
        if (hero.getMp5sGrowth() != null) {
            kvList.add("mp_5s_growth = " + hero.getMp5sGrowth());
        }
        if (hero.getMp5sStart() != null) {
            kvList.add("mp_5s_start = " + hero.getMp5sStart());
        }
        if (hero.getAttackSpeedMax() != null) {
            kvList.add("attack_speed_max = " + hero.getAttackSpeedMax());
        }
        if (hero.getRoleMain() != null) {
            kvList.add("role_main = '" + hero.getRoleMain());
        }
        if (hero.getRoleAssist() != null) {
            kvList.add("role_assist = '" + hero.getRoleAssist() + "'");
        }
        if (hero.getBirthdate() != null) {
            kvList.add("birthdate = '" + hero.getBirthdate().toString() + "'");
        }
        if (kvList.isEmpty()) {
            System.out.println("update nothing");
            return;
        }
        builder.append(String.join(", ", kvList));
        builder.append(" WHERE id = ").append(hero.getId());
        String sql = builder.toString();
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement()) {
            int result = stat.executeUpdate(sql);
            System.out.println("updateById result = " + result);
        }
    }

    public void removeById(int id) throws SQLException {
        String sql = "DELETE FROM heroes WHERE id = " + id;
        try (Connection conn = getConnection();
             Statement stat = conn.createStatement()) {
            int result = stat.executeUpdate(sql);
            System.out.println("removeById result = " + result);
        }
    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(dbUrl, dbUser, dbPassword);
    }
}
